15  写入Excel文件

15.1 引言数据导出的必要性与Excel在金融领域的地位

在金融数据分析和商业智能项目中,数据导出是工作流程的关键环节。分析人员经常需要将处理好的数据导出为Excel格式,原因包括:

  1. 报告生成: 向管理层或客户呈现分析结果
  2. 跨部门协作: 与非技术人员(如业务部门、财务部门)共享数据
  3. 审计合规: 保存分析过程的中间结果和最终输出
  4. 进一步分析: 利用Excel的透视表、图表等功能进行探索性分析

补充说明:Excel文件格式的技术演进

Excel文件经历了多次格式演变,每种格式都有其特点:

格式 扩展名 特点 适用场景
XLS .xls Excel 97-2003格式,专有二进制格式 兼容老版本Excel
XLSX .xlsx Excel 2007+格式,基于Office Open XML标准 现代Excel工作的标准格式
XLSB .xlsb 二进制格式,文件更小,加载更快 大数据量场景
CSV .csv 纯文本,逗号分隔值 跨平台数据交换

Pandas主要通过openpyxl引擎写入XLSX文件,通过xlsxwriter引擎实现高级格式化功能。

15.2 to_excel函数基础导出方法

数学背景:数据序列化与持久化

数据持久化(Data Persistence)是将程序中的数据保存到非易失性存储设备(如硬盘)的过程。在写入Excel时,需要处理以下技术挑战:

  1. 数据类型映射: Python类型 → Excel类型
    • int64 → Excel数值
    • float64 → Excel数值
    • datetime64 → Excel日期时间
    • bool → Excel逻辑值
    • str → Excel文本
  2. 特殊值处理:
    • NaN (Not a Number) → 缺失值标记
    • inf (无穷大) → Excel的#NUM!错误或自定义表示
    • -inf (负无穷大) → 同上
列表 15.1 
# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
import numpy as np  # 导入NumPy数值计算库
import pandas as pd  # 导入Pandas数据分析库
import datetime as dt  # 导入日期时间处理模块

data=[[dt.datetime(2020,1,1, 10, 13), 2.222, 1, True],  # 定义列表data
      [dt.datetime(2020,1,2), np.nan, 2, False],  # 第二行数据（含缺失值NaN）
      [dt.datetime(2020,1,2), np.inf, 3, True]]  # 第三行数据（含无穷大inf）
df = pd.DataFrame(data=data,columns=["Dates", "Floats", "Integers", "Booleans"])  # 创建数据框df
df.index.name="index"  # 设置数据框索引列的名称

# 将数据框导出至Excel文件，指定工作表名和写入参数
df.to_excel("written_with_pandas1.xlsx", sheet_name="Output",
            startrow=1, startcol=1, index=True, header=True,  # 设置写入Excel时的起始行列位置和索引/表头选项
            na_rep="<NA>", inf_rep="<INF>")  #
            
with pd.ExcelWriter("written_with_pandas2.xlsx") as writer:  # 使用上下文管理器
  # 将数据框写入Sheet1工作表的第2行第2列位置
  df.to_excel(writer, sheet_name="Sheet1", startrow=1, startcol=1)
  # 将数据框再次写入Sheet1工作表的第11行位置
  df.to_excel(writer, sheet_name="Sheet1", startrow=10, startcol=1)
  df.to_excel(writer, sheet_name="Sheet2")  # 将数据框写入Excel文件

print(df)  # 输出数据框数据

代码深度解析:

15.2.1 第一部分to_excel函数参数详解

  1. startrow和startcol参数的作用:

    • startrow=1: 数据从Excel的第2行开始(第1行留给其他内容,如报告标题)
    • startcol=1: 数据从Excel的第2列开始(第1列留给行号或其他标识)
    • 这种灵活性允许在一个Excel文件中创建复杂的布局,如多表格报告

  2. 特殊值处理参数:

    • na_rep: 控制缺失值的显示方式
      • 默认:空单元格
      • 本例中: "<NA>"明确标记缺失值
      • 其他常用值: "NA", "-", "NULL"
    • inf_rep: 控制无穷大的显示方式
      • 默认:Excel会显示#NUM!错误
      • 本例中: "<INF>"自定义表示
      • 其他常用值: "Infinity", "∞"

  3. 数据类型转换:

    # Pandas内部执行的数据类型映射
- datetime64[ns] → Excel日期时间(序列号)
- float64 (NaN) → Excel空单元格(或na_rep指定的值)
- float64 (inf) → Excel的#NUM!错误(或inf_rep指定的值)
- bool → Excel的TRUE/FALSE

15.2.2 第二部分ExcelWriter类的优势

理论背景:上下文管理器与资源管理

ExcelWriter使用了Python的上下文管理器(Context Manager)模式,通过with语句实现:

with pd.ExcelWriter("file.xlsx") as writer:
    # 执行操作
# 自动关闭文件,释放资源

这种设计的优势: - 自动资源管理: 无论是否发生异常,文件都会正确关闭 - 异常安全: 即使写入过程中出错,也能保证文件完整性 - 代码简洁: 不需要显式调用close()方法

同一工作表多次写入的原理: - ExcelWriter不会覆盖已有内容,而是从指定位置开始写入 - 这允许创建复杂的报表布局,如: [标题区] [数据表1] [空行] [数据表2]

多工作表管理的优势: - 数据分区: 将不同类型的数据放在不同工作表 - Sheet1: 原始数据 - Sheet2: 计算指标 - Sheet3: 图表数据 - 权限控制: 可以对不同工作表设置不同的访问权限 - 性能优化: 大数据集可以分成多个工作表,提高加载速度

to_excel vs ExcelWriter对比:

特性 to_excel ExcelWriter
单工作表 ✓ ✓
多工作表 ✗ ✓
同表多次写入 ✗ ✓
代码复杂度 简单 稍复杂
资源管理 自动 需用with语句

15.3 实际应用场景

  1. 财务报表导出: 将计算好的财务指标导出为Excel,供审计使用
  2. 交易报告生成: 每日交易结束后,生成交易汇总报告
  3. 数据存档: 将处理后的历史数据保存为Excel,便于离线分析

补充说明:大数据量处理策略

当处理大规模金融数据时(如百万级交易记录),直接导出到Excel可能遇到性能瓶颈:

  1. Excel的行数限制:
    • XLS格式(旧版): 65,536行
    • XLSX格式(新版): 1,048,576行
  2. 性能优化策略:
    • 分批写入:将大数据集分成多个小文件
    • 数据抽样:导出代表性样本
    • 聚合后导出:先汇总再导出
  3. 文件大小优化:
    • 使用XLSB二进制格式(文件更小)
    • 压缩数据(删除不必要的列、降低精度)
    • 分割文件(按时间、类别等)

易混淆概念辨析:to_csv vs to_excel

特性 CSV Excel
文件大小 小(纯文本) 大(包含格式)
读取速度 快 慢
支持多工作表 ✗ ✓
支持格式化 ✗ ✓
跨平台兼容性 极好 需Excel
适用场景 数据交换、大数据集 报告、可视化

选择建议: - 数据备份/迁移 → CSV - 向非技术人员展示 → Excel - 大数据量(>100万行) → CSV或数据库 - 需要多表格报告 → Excel